import pandas as pd
from django.http import JsonResponse
from rest_framework.views import APIView
from app01 import models
from app01.woutils import user_jwt
from django.http import HttpResponse





class Ap1_api(APIView):
    '''项目1接口'''
    def get(self,request):
        '''获取列表'''
        #提取分页参数
        try:
            page=int(request.query_params["page"])
            pagesize =int(request.query_params["pagesize"])
        except Exception as e:
            page = 1
            pagesize = 10
        if page < 1 :
            page = 1
        start_page = page * pagesize -pagesize
        end_page = page * pagesize

        #提取搜索字段参数
        dic = {}
        project = request.query_params.get("project","")
        mode = request.query_params.get("mode","")
        msg = request.query_params.get("msg","")
        name = request.query_params.get("name","")
        remark = request.query_params.get("remark","")

        #检测是否填写了这些搜索参数  如果填写了 就使用模糊搜索进行搜索
        if project:
            dic["project__icontains"] = project
        if mode:
            dic["mode__icontains"] = mode
        if msg:
            dic["msg__icontains"] = msg
        if name:
            dic["name__icontains"] = name
        if remark:
            dic["remark__icontains"] = remark

        #按照条件进行搜索      .order_by('-id')是按id倒序进行排列
        datas = models.Ap1Info.objects.filter(**dic).order_by('-id')[start_page:end_page].values()
        #转换为py的列表格式
        # datas = [item for item in datas]
        data_list = []
        for item in datas:
            item['CreatedTime'] = item['CreatedTime'].strftime("%Y-%m-%d %H:%M:%S")
            item['UpdatedTime'] = item['UpdatedTime'].strftime("%Y-%m-%d %H:%M:%S")
            data_list.append(item)
        #查询总条数
        total = models.Ap1Info.objects.filter(**dic).count()
        #制作返回json
        data = {
            "code": 200,
            "msg": "查询成功",
            "data": {"total":total,
                    "data":data_list,
                     }
        }
        return JsonResponse(data)


    def post(self,request):
        '''新增数据'''
        #获取提交数据
        project= request.data.get('project',"")
        mode= request.data.get('mode',"")
        name= request.data.get('name',"")
        msg= request.data.get('msg',"")
        count= request.data.get('count',"")
        price= request.data.get('price',"")
        remark= request.data.get('remark',"")
        #制作为字典
        dic={
            "project":project,
            "mode": mode,
            "name":name,
            "msg":msg,
            "count":count,
            "price":price,
            "remark":remark,
        }
        #进行写入
        try:
            models.Ap1Info.objects.create(**dic)
        except Exception as e:
            data = {'code': 503, "msg": "新增失败", 'data': ""}
            return JsonResponse(data)
        data = {'code': 200, "msg": "新增成功", 'data': ""}
        return JsonResponse(data)


    def delete(self,request):
        '''删除数据'''
        #获取需要删除的id
        ids = request.query_params.get("id","")
        if ids == "":
            data = {'code': 503,"msg":"删除参数为空", 'data':""}
            return JsonResponse(data)
        #将字符串切割为列表
        ids = ids.split(",")
        ids_int = []
        for i in ids:
            if i == "":
                continue
            ids_int.append(int(i))  #将id转换为数字类型 然后添加到新的列表中

        #id__in为 根据id字段 传入一个列表 批量查询需要删除的数据
        Obj=models.Ap1Info.objects.filter(id__in=ids_int)
        #检测数据是否存在
        if not Obj.exists():
            data = {'code': 503, "msg": "未找到数据", 'data': ""}
            return JsonResponse(data)
        #删除查询到的数据
        Obj.delete()
        data = {'code': 200, "msg": "删除成功", 'data': ""}
        return JsonResponse(data)


    def put(self,request):
        '''修改数据'''
        #提取需要修改的数据
        uid = request.data.get("id")
        project= request.data.get('project',"")
        mode= request.data.get('mode',"")
        name= request.data.get('name',"")
        msg= request.data.get('msg',"")
        count= request.data.get('count',"")
        price= request.data.get('price',"")
        remark= request.data.get('remark',"")

        #制作为字典
        dic={
            "project":project,
            "mode": mode,
            "name":name,
            "msg":msg,
            "count":count,
            "price":price,
            "remark":remark,
        }
        #查询数据是否存在
        data_obj = models.Ap1Info.objects.filter(id=uid)
        if data_obj.exists():
            #存在就进行更新
            data_obj.update(**dic)
            data = {
                "code": 200,
                "msg": "修改成功",
                "data": dic
            }
            return JsonResponse(data)
        else:
            data = {
                "code": 503,
                "msg": "数据不存在",
                "data": ""
            }
            return JsonResponse(data)


#导入的xlsx字段对应的数据库字段
excel_dic = {
    "项目": "project",
    "类别": "mode",
    "名称": "name",
    "信息": "msg",
    "数量": "count",
    "单价": "price",
    "备注": "remark",
}

class Ap1_excel(APIView):
    '''excel的上传 和 下载'''
    def get(self,request):
        '''导出'''
        token =request.query_params.get('AUTHORIZATION',"")  # 获取token
        token_dic, err = user_jwt.ParseToekn(token)
        if err != None:
            data = {
                "code": 403,
                "msg": "未登录或登录已过期",
                "data": ""
            }
            return JsonResponse(data)

        #提取搜索条件
        dic = {}
        project = request.query_params.get("project","")
        mode = request.query_params.get("mode","")
        msg = request.query_params.get("msg","")
        name = request.query_params.get("name","")
        remark = request.query_params.get("remark","")

        if project:
            dic["project__icontains"] = project
        if mode:
            dic["mode__icontains"] = mode
        if msg:
            dic["msg__icontains"] = msg
        if name:
            dic["name__icontains"] = name
        if remark:
            dic["remark__icontains"] = remark
        #查询对应的数据
        data=models.Ap1Info.objects.filter(**dic).values()

        #将查询到的数据进行格式转换
        datas=[]
        for i in data:
            newi = {}
            newi['创建时间'] = i['CreatedAt'].strftime("%Y-%m-%d %H:%M:%S")
            newi['最近修改时间']  = i['UpdatedAt'].strftime("%Y-%m-%d %H:%M:%S")
            newi['项目'] = i['project']
            newi['类别'] = i['mode']
            newi['名称'] = i['name']
            newi['信息'] = i['msg']
            newi['数量'] = i['count']
            newi['单价'] = i['price']
            newi['备注'] = i['remark']
            datas.append(newi)
        #将数据制作为pandas的表格
        df = pd.DataFrame(datas)
        #设置响应信息
        response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        response['Content-Disposition'] = 'attachment; filename="导出数据.xlsx"'
        #将表格写入到响应中
        df.to_excel(response)
        #返回表格文件
        return response


    def post(self,request):
        '''批量上传'''

        #限权验证
        token = request.META.get('HTTP_AUTHORIZATION')  # 获取token
        token_dic, err = user_jwt.ParseToekn(token)
        if err != None:
            data = {
                "code": 403,
                "msg": "未登录或登录已过期",
                "data": ""
            }
            return JsonResponse(data)


        #读取上传的文件
        file_obj = request.FILES.get('file')

        #将文件用pandas进行解析
        df = pd.read_excel(file_obj)
        #将空行替换为0  避免默认的nan
        df.fillna(0, inplace=True)

        #对上传文件进行格式转换
        data_create = []
        #将每一行转换为对应的字典格式
        df = df.to_dict("records")
        #将表格头的中文转换为对应的数据库字段变量名
        for i in df:
            new_i={}
            try:
                for k,v in excel_dic.items():
                    new_i[v] = i[k]
            except Exception as e:
                data = {"code": 503, "msg": "导入失败 数据格式错误", "data": str(e)}
                return JsonResponse(data)
            #添加到列表中
            data_create.append(models.Ap1Info(**new_i))
        try:
            #将处理好的数据写入到数据库中
            models.Ap1Info.objects.bulk_create(data_create)
            data={"code":200,"msg":"导入成功","data":""}
            return JsonResponse(data)
        except Exception as e:
            data = {"code": 503, "msg": "导入失败", "data": str(e)}
            return JsonResponse(data)